﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WaveMaster.Holidays.Club.Gateway;
using WaveMaster.Holidays.Club.Domain;
using System.Collections;
using MySql.Data.MySqlClient;
using WaveMaster.Holidays.Club.SQL;

namespace WaveMaster.Holidays.Club.Service
{
    public class TripService : IService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        CheckInService m_CheckInService;

        public TripService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
            m_CheckInService = new CheckInService();
        }

        public TripService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_CheckInService = new CheckInService();
        }

        public IList LoadAllTrip()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.GetAllTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Trip user = new Trip(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList LoadAllTripByDateAndSeaPort(DateTime date, long seaPortID)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.GetAllTripByDateAndSeaPort(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_seaPortID", MySqlDbType.Int32).Value = seaPortID;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = date;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Trip user = new Trip(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList LoadAllTripByDateAndSeaPort(DateTime date, long seaPortID, DateTime biggerthen)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.GetAllTripByDateAndSeaPortBiggerDateTime(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_seaPortID", MySqlDbType.Int32).Value = seaPortID;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = date;
                cmd.Parameters.Add("m_departureTime", MySqlDbType.Datetime).Value = biggerthen;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Trip user = new Trip(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public Trip GetTripByID(long id)
        {
            Trip result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.GetTripByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Trip(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList SearchTripByCodeNameAndDepartureDate(string p, DateTime date)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.SearchTripByCodeNameAndDepartureDate(p), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Date).Value = date;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Trip(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void DeleteTrip(Trip user)
        {
            try
            {
                CheckIn checkin = m_CheckInService.SearchCheckInByTrip(user);
                m_CheckInService.DeleteCheckIn(checkin);
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.DeleteTripByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = user.m_tripID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdateTrip(Trip m_user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.UpdateTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_tripCode", MySqlDbType.VarChar).Value = m_user.m_tripCode;
                cmd.Parameters.Add("m_avaliable", MySqlDbType.VarChar).Value = m_user.m_avaliable;
                cmd.Parameters.Add("m_vesselID", MySqlDbType.Int32).Value = m_user.m_vessel.m_vesselID;
                cmd.Parameters.Add("m_seaPortID", MySqlDbType.Int32).Value = m_user.m_seaPort.m_seaPortID;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = m_user.m_departureDate;
                cmd.Parameters.Add("m_departureTime", MySqlDbType.Datetime).Value = m_user.m_departureTime;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = m_user.m_modfctnBy;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = m_user.m_tripID;
                cmd.Parameters.Add("m_destinationPortID", MySqlDbType.Int32).Value = m_user.m_destinationPortID.m_seaPortID;
                cmd.Parameters.Add("m_departureStatus", MySqlDbType.VarChar).Value = m_user.m_departureStatus;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            } 
        }

        public void InserTrip(Trip trip)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(TripSQL.InsertTrip(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_tripCode", MySqlDbType.VarChar).Value = trip.m_tripCode;
                cmd.Parameters.Add("m_avaliable", MySqlDbType.VarChar).Value = trip.m_avaliable;
                cmd.Parameters.Add("m_vesselID", MySqlDbType.Int32).Value = trip.m_vessel.m_vesselID;
                cmd.Parameters.Add("m_seaPortID", MySqlDbType.Int32).Value = trip.m_seaPort.m_seaPortID;
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = trip.m_departureDate;
                cmd.Parameters.Add("m_departureTime", MySqlDbType.Datetime).Value = trip.m_departureTime;
                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = trip.m_cretnBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = trip.m_cretnBy;
                cmd.Parameters.Add("m_destinationPortID", MySqlDbType.Int32).Value = trip.m_destinationPortID.m_seaPortID;
                cmd.Parameters.Add("m_departureStatus", MySqlDbType.VarChar).Value = trip.m_departureStatus;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                //---get ID
                cmd.Parameters.Clear();
                cmd.CommandText = TripSQL.GetMaxTripID();
                trip.m_tripID = Convert.ToInt32(cmd.ExecuteScalar());
                //
                cmd.CommandText = AutoNumberSQL.InsertAutoNumber();
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = 0;
                cmd.Parameters.Add("type", MySqlDbType.VarChar).Value = "BoardingPassCode";
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = trip.m_tripID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                //---02-02-2013---------insert checkIn;
                cmd.CommandText = BookingSQL.InsertBooking();
                cmd.Parameters.Add("m_bookingCode", MySqlDbType.VarChar).Value = trip.m_tripCode + "-" + trip.m_departureTime.ToString("ddMMyy") + "-" + trip.m_tripID.ToString();
                cmd.Parameters.Add("m_groupName", MySqlDbType.VarChar).Value = "";
                cmd.Parameters.Add("m_bookNumber", MySqlDbType.Decimal).Value = 0;
                cmd.Parameters.Add("m_lastBalance", MySqlDbType.Decimal).Value = 0;
                cmd.Parameters.Add("m_tripID", MySqlDbType.Int32).Value = trip.m_tripID;
                cmd.Parameters.Add("m_returntrip", MySqlDbType.Int32).Value = 0;
                cmd.Parameters.Add("m_ticketType", MySqlDbType.VarChar).Value =  TicketType.OneWay;
                cmd.Parameters.Add("m_travelAgentId", MySqlDbType.Int32).Value = 1;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = "";
                cmd.Parameters.Add("m_bookingStatus", MySqlDbType.VarChar).Value = CheckInStatus.CheckIn;
                cmd.Parameters.Add("m_returnDate", MySqlDbType.Datetime).Value = new DateTime(1900,1,1);
                cmd.Parameters.Add("m_departureDate", MySqlDbType.Datetime).Value = trip.m_departureDate;
                cmd.Parameters.Add("m_bookingDate", MySqlDbType.Datetime).Value = DateTime.Today;

                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = trip.m_cretnBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = trip.m_cretnBy;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
